/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database_client;


import java.io.File;
import java.sql.*;
import java.util.Vector;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Attr;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 *
 * @author suzunghia
 */
public class ParseDatabase {

    Connection connect = null;
    DatabaseMetaData dbmd = null;
    String strQuery = null;
    Vector strColumns = null;
    public void Connect() {
        try {

            /*
             * Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con =
             * DriverManager.getConnection("jdbc:odbc:ProductDS");
             */
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            connect = DriverManager.getConnection("jdbc:sqlserver://localhost:49782;databaseName=SINHVIEN;user=sa;password=neverout");
            dbmd = connect.getMetaData();
            
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }

    }

    public void CreateDatabase(String name, String path) {
        try {
            DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
            DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
            
            //Tao tai lieu
            Document doc = docBuilder.newDocument();
            
            //Tao node goc
            Element rootElement = doc.createElement("Database");
            doc.appendChild(rootElement);
            Attr attr = doc.createAttribute("name");
            attr.setValue(name);
            rootElement.setAttributeNode(attr);            
            String[] type = {"TABLE"};
            ResultSet rs = dbmd.getTables(null, null, "%", type);
            
            while (rs.next()) {
                
                //Tao node table
                Element tableElement = doc.createElement("Table");
                rootElement.appendChild(tableElement);
                String str = rs.getString(3);
                attr = doc.createAttribute("Name");
                attr.setValue(str);
                tableElement.setAttributeNode(attr);
                strColumns = new Vector();
                //Tao node header va type
                Element headerElement = doc.createElement("Header");
                Element typeElement = doc.createElement("Type");
                ResultSet rsColumn = dbmd.getColumns(null, null, str, null);
                int count = 0;
                while (rsColumn.next()) {
                    String strColumn = rsColumn.getString("COLUMN_NAME");
                    strColumns.add(strColumn);
                    String strType = rsColumn.getString("TYPE_NAME");
                    Attr attrHeader = doc.createAttribute(strColumn);
                    attrHeader.setValue(Integer.toString(count));
                    headerElement.setAttributeNode(attrHeader);
                    Attr attrType = doc.createAttribute(strColumn);
                    attrType.setValue(strType);
                    typeElement.setAttributeNode(attrType);
                    count++;
                }
                tableElement.appendChild(headerElement);
                tableElement.appendChild(typeElement);
                
                Statement stmt = connect.createStatement();
                strQuery = "select * from " + str;
                ResultSet records = stmt.executeQuery(strQuery);
                while (records.next())
                {
                    Element dataElement = doc.createElement("Data");
                    tableElement.appendChild(dataElement);
                    for(int i = 0; i < strColumns.size(); i++)
                    {
                        Attr attrColumn = doc.createAttribute((String)strColumns.get(i));
                        attrColumn.setValue(records.getNString((String)strColumns.get(i)).trim());
                        dataElement.setAttributeNode(attrColumn);
                    }
                }
            }
            //Ghi ra file
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            DOMSource source = new DOMSource(doc);
            StreamResult result = new StreamResult(new File("D:\\file.xml"));
            transformer.transform(source, result);
            connect.close();
            System.out.println("OK");
        } catch (Exception ex) {
            System.out.println(ex.toString());
        }
    }
}
